﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMSSyncData.Model.WMSModel;
using WMSSyncData.Utils;

namespace WMSSyncData.DAL.SqlDB
{
    public class WMS_T_InStockRMRowsDAL : BaseSqlDBDAL<WMS_T_InStockRMRows>
    {
        public WMS_T_InStockRMRows Get(string relationBill, string OrderNo, string rowNo)
        {

            var sql = string.Format(@"select IR.* from wms_T_InStockRMRows IR INNER JOIN wms_T_InStockRM I on i.BillNo=ir.BillNo
						where i.Status='0' and  IR.RelatedBill = '{0}' 
                                and IR.RowNo = '{1}' and IR.OrderNo = '{2}' and ISNULL(IR.RealQty,0)=0", relationBill, rowNo, OrderNo);

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];

            var list = MakeTablePackage(dt);

            var result = list.FirstOrDefault();

            return result;
        }

        public DataTable GetInStock(string relationBill)
        {

            var sql = string.Format(@"SELECT BillNo FROM dbo.wms_T_InStockRows WHERE BillNo IN (
SELECT BillNo FROM dbo.wms_T_InStock 
WHERE RelatedBill='{0}'  and status='0') AND ISNULL(RealQty,0)=0", relationBill);

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }


        public DataTable GetUpdateBOMOutStockRowData(List<WMS_T_OutStockRMRows> list) //可修改
        {
            var sqlWhere = "and (";
            foreach (WMS_T_OutStockRMRows entity in list)
            {

                sqlWhere = sqlWhere + " (RelatedBill='" + entity.RelatedBill + "' and RowNo='" + entity.RowNo + "') or";


            }
            sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 2);
            sqlWhere = sqlWhere + ")";
            var sql = string.Format(@"select * from wms_T_OutStockRMRows where RealQty=0 ", sqlWhere);

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }

        public bool UpdateBOMRowInfo(List<WMS_T_OutStockRMRows> list)
        {
            bool result = false;
            var sql = "";
            foreach (WMS_T_OutStockRMRows entity in list)
            {
                sql = string.Format(@"update wms_T_OutStockRMRows set PartNumber='{0}',PlanQty='{1}',Unit='{2}',Ext1='{3}',Ext3='{4}' where RelatedBill='" + entity.RelatedBill + "' and RowNo='" + entity.RowNo + "';", entity.PartNumber, entity.PlanQty, entity.Unit, entity.Ext1, entity.Ext3);
            }

            var res = SqlDBHelper.ExcuteSQL(sql);

            if (res > 0)
            {
                result = true;
            }

            return result;
        }

        public bool FilterPartNumber()
        {
            var result = false;
            var sql1 = "insert into Filter_InStockRMRows select * FROM wms_T_InStockRMRows WHERE PartNumber in (SELECT PartNumber From wms_T_FilterPartNumber) AND Status = 0;";
            var sql2 = "Delete FROM wms_T_InStockRMRows WHERE PartNumber in (SELECT PartNumber From wms_T_FilterPartNumber) AND Status = 0;";
            var res = SqlDBHelper.ExcuteSQL(sql1 + sql2);

            if (res > 0)
            {
                result = true;
            }

            return result;
        }

        public bool UpdateInStockRMStatus(string billNo, int rowno)
        {
            var result = false;
            var sql = string.Format(@"UPDATE dbo.wms_T_InStockRM SET Status='D' WHERE BillNo='{0}'  AND Status='0';
                                    UPDATE dbo.wms_T_InStockRMRows SET Status='D' WHERE BillNo='{0}' AND RowNo={1} AND Status='0';", billNo, rowno);

            var res = SqlDBHelper.ExcuteSQL(sql);

            if (res > 0)
            {
                result = true;
            }

            return result;
        }

        public bool UpdateInStockStatus(string billNo)
        {
            var result = false;
            var sql = string.Format(@"UPDATE dbo.wms_T_InStock SET Status='D' WHERE BillNo='{0}'   AND Status='0';", billNo);

            var res = SqlDBHelper.ExcuteSQL(sql);

            if (res > 0)
            {
                result = true;
            }

            return result;
        }

        public bool UpdateInStockRMRowsPlanQty(string billNo, decimal planQty, int rowno)
        {
            var result = false;

            var sql = string.Format(@"UPDATE dbo.WMS_T_InStockRMRows SET PlanQty={0} WHERE BillNo='{1}' AND RowNo={2} AND ISNULL(RealQty,0)=0", planQty, billNo, rowno);
            var res = SqlDBHelper.ExcuteSQL(sql);
            if (res > 0)
            {
                result = true;
            }
            return result;
        }

        public bool UpdateInStockRowsPlanQty(string billNo, int planQty)
        {
            var result = false;

            var sql = string.Format(@"UPDATE dbo.WMS_T_InStockRows SET PlanQty={0} WHERE BillNo='{1}'  AND ISNULL(RealQty,0)=0", planQty, billNo);
            var res = SqlDBHelper.ExcuteSQL(sql);
            if (res > 0)
            {
                result = true;
            }
            return result;
        }

        public DataTable GetStorageSN()
        {

            var sql = string.Format(@"select Storage_SN from [dbo].[T_Bllb_Storage_tbs] where Storage_Type='201'");

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }

        public DataTable GetStorage101SN()
        {

            var sql = string.Format(@"select Storage_SN from [dbo].[T_Bllb_Storage_tbs] where Storage_Type='101'");

            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }

        public DataTable getDeleteRows(List<WMS_T_InStockRMRows> list)
        {
            string sqlWhere = string.Empty;
            if (list.Count() > 0)
            {
                sqlWhere = " and (";
                foreach (var item in list)
                {
                    sqlWhere = sqlWhere + string.Format(" (RelatedBill='{0}' and RowNo='{1}' and OrderNo='{2}') or", item.RelatedBill, item.RowNo, item.OrderNo);
                }
                sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 2) + ") ";
            }
            else
            {
                sqlWhere = " and 1<>1";
            }
            var sql = "select *,(select count(1) from wms_T_InStockRMRows a where a.billno=wms_T_InStockRMRows.billno) as billcount  from wms_T_InStockRMRows where 1=1 " + sqlWhere + " and billno in(select billno from wms_T_InStockRM where Status='0')";
            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];
            return dt;
        }

        public bool DeleteRowAndInfo(DataTable dtRow, List<string> InfoList)
        {
            var result = false;

            string sql = string.Empty;
            foreach (DataRow dr in dtRow.Rows)
            {
                sql = sql + string.Format(@"delete wms_T_InStockRMRows   WHERE BillNo='{0}' and RowNo='{1}' and OrderNo='{2}';", dr["BillNo"].ToString(), dr["RowNo"].ToString(), dr["OrderNo"].ToString());
            }
            //foreach (string billno in InfoList)
            //{
            //    sql = sql + string.Format(@"delete wms_T_InStockRM   WHERE BillNo='{0}' ;", billno);
            //}
            var res = SqlDBHelper.ExcuteSQL(sql);
            if (res > 0)
            {
                result = true;
            }
            return result;
        }


        public bool DeleteProRowAndInfos(DataTable dtRow)
        {
            var result = false;

            string sql = string.Empty;
            foreach (DataRow dr in dtRow.Rows)
            {
                sql = sql + string.Format(@"delete wms_T_InStockRows   WHERE BillNo='{0}' and RowNo='{1}' and SN='{2}';delete wms_T_InStock WHERE BillNo='{0}'", dr["BillNo"].ToString(), dr["RowNo"].ToString(), dr["OrderNo"].ToString());
            }

            var res = SqlDBHelper.ExcuteSQL(sql);
            if (res > 0)
            {
                result = true;
            }
            return result;
        }

        public DataTable GetSyncProDeleteData(DataTable dtD)
        {

            var sql = @"select a.RelatedBill,b.BillNo,b.RowNo,b.SN as OrderNo from wms_T_InStock a inner join wms_T_InStockRows b on a.billno=b.billno where a.Status='0' and BillType='成品入库'";
            string sqlWhere = "";
            if (dtD != null && dtD.Rows.Count > 0)
            {
                sqlWhere = " and (";
                foreach (DataRow item in dtD.Rows)
                {
                    sqlWhere = sqlWhere + string.Format(" (a.RelatedBill='{0}' and b.RowNo='{1}' and b.SN='{2}' ) or", item["RelatedBill"].ToString(), item["RowNo"].ToString(), item["SN"].ToString());
                }
                sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 2) + ")";
            }
            else
            {
                sqlWhere = " and 1<>1";
            }
            sql = sql + sqlWhere;
            var dt = SqlDBHelper.GetDataSet(sql).Tables[0];

            return dt;
        }


    }
}
